﻿using Liu_Project.Dal.Common;
using Liu_Project.Entity.Admin;
using LSL.SqlClient;
using System;
using System.Data;
using System.Text;
using Liu_Project.Utils;

namespace Liu_Project.Dal.Admin
{
    public class UserRoleDal : DalBase
    {
        private readonly StringBuilder sb = new StringBuilder();

        /// <summary>
        /// 获取用户列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetUserList(string sqlWhere, string sortField, string sortOrder, int page, int pageSize, ref int total)
        {
            var sqlHelper = new SQLHelper() { IsAutoClose = false };
            try
            {
                DataTable dt = null;

                string TbUser = sqlHelper.GetTempTable();
                sb.Clear();
                sb.AppendLine("SELECT A.UserId,A.UserNo,A.UserName,ISNULL(A.Img,'/Admin/images/admin.jpg') Img,A.Phone,A.Email");
                sb.AppendLine(",A.LastLoginTime,A.UpdateTime,A.UpdateUserId,B.UserNo CreateUserNo,B.UserName CreateUserName");
                sb.AppendLine(",A.IsVisiable,A.CreateTime,A.CreateUserId,C.UserNo UpdateUserNo,C.UserName UpdateUserName,A.Version");
                sb.AppendLine("INTO " + TbUser);
                sb.AppendLine("FROM EntUser A WITH(NOLOCK)");
                sb.AppendLine("LEFT JOIN EntUser B WITH(NOLOCK) ON A.CreateUserId=B.UserId");
                sb.AppendLine("LEFT JOIN EntUser C WITH(NOLOCK) ON A.UpdateUserId=C.UserId");
                sb.AppendLine("WHERE A.UserNo!='admin'");
                sb.AppendLine(sqlWhere);
                sqlHelper.ExecuteNonQuery(sb.ToString());

                dt = sqlHelper.GetPageTableList(TbUser, sortField, sortOrder, pageSize, page, ref total);

                return dt;
            }
            finally
            {
                sqlHelper.Close();
            }
        }

        /// <summary>
        /// 保存或更新用户信息
        /// </summary>
        /// <param name="_state">编辑状态</param>
        /// <param name="oldPwd">旧密码</param>
        /// <param name="entUser">用户</param>
        public void Save(string _state, string oldPwd, EntUser entUser, DataTable roleTable)
        {
            TryTran((sqlHelper, dal) =>
            {
                if (_state == ActionOptions.Add)
                {
                    var user = GetListByField<EntUser>("UserNo", entUser.UserNo);
                    if (user.Count > 0)
                        throw new Exception("当前用户编号已存在,请更改.");
                    entUser.Password = Function.GetMD5(entUser.Password);
                    entUser.LastLoginTime = DateTime.Now;
                    SetCreateDefaultValue(entUser);
                    entUser.UserId = DataConvert.ToLong(dal.InsertByEntity(entUser, false, sqlHelper));
                }
                else if (_state == ActionOptions.Edit)
                {
                    var item = GetEntityById<EntUser>(entUser.UserId.ToString());
                    if (Encoding.Default.GetString(item.Version) != Encoding.Default.GetString(entUser.Version))
                        throw new Exception("当前用户信息已被另外一个操作员修改,请重新刷新.");
                    if (item.UserNo != entUser.UserNo)
                    {
                        var user = GetListByField<EntUser>("UserNo", entUser.UserNo);
                        if (user.Count > 0)
                            throw new Exception("当前用户编号已存在,请更改.");
                    }

                    if (!string.IsNullOrWhiteSpace(oldPwd))
                    {
                        if (Function.GetMD5(oldPwd) != item.Password)
                            throw new Exception("当前密码与登录密码不匹配.");
                        if (oldPwd == entUser.Password)
                            throw new Exception("新密码与登录密码一致,不需要修改.");
                    }

                    if (string.IsNullOrWhiteSpace(entUser.Password))
                        entUser.Password = item.Password;
                    else
                        entUser.Password = Function.GetMD5(entUser.Password);
                    if (!string.IsNullOrWhiteSpace(entUser.Phone))
                        entUser.Phone = item.Phone;
                    if (!string.IsNullOrWhiteSpace(entUser.Email))
                        entUser.Email = item.Email;
                    SetUpdateDefaultValue(entUser);
                    dal.UpdateByEntity(entUser, false, sqlHelper);
                }

                if (roleTable.Rows.Count > 0)
                {
                    var TbRole = sqlHelper.DataTableToTempTableStr(roleTable);
                    sb.Clear();
                    sb.AppendLine("DELETE FROM dbo.EntRoleUser WHERE UserId=@CurrUserId");
                    sb.AppendLine("INSERT INTO dbo.EntRoleUser");
                    sb.AppendLine("(");
                    sb.AppendLine("    RoleId,");
                    sb.AppendLine("    UserId,");
                    sb.AppendLine("    CreateTime,");
                    sb.AppendLine("    CreateUserId,");
                    sb.AppendLine("    CreateUserName,");
                    sb.AppendLine("    UpdateTime,");
                    sb.AppendLine("    UpdateUserId,");
                    sb.AppendLine("    UpdateUserName");
                    sb.AppendLine(")");
                    sb.AppendLine("SELECT");
                    sb.AppendLine("    value,         -- RoleId - bigint");
                    sb.AppendLine("    @CurrUserId,         -- UserId - bigint");
                    sb.AppendLine("    GETDATE(), -- CreateTime - datetime");
                    sb.AppendLine("    @UserId,         -- CreateUserId - bigint");
                    sb.AppendLine("    @UserName,       -- CreateUserName - nvarchar(50)");
                    sb.AppendLine("    GETDATE(), -- UpdateTime - datetime");
                    sb.AppendLine("    @UserId,         -- UpdateUserId - bigint");
                    sb.AppendLine("    @UserName        -- UpdateUserName - nvarchar(50)");
                    sb.AppendLine("FROM " + TbRole);

                    var pList = new SqlParameterList();
                    pList.Add("CurrUserId", entUser.UserId);
                    pList.Add("UserId", CurrentUser.UserId);
                    pList.Add("UserName", CurrentUser.UserName);
                    sqlHelper.ExecuteNonQuery(sb.ToString(), pList);
                }
            });
        }

        /// <summary>
        /// 获取用户登录日志
        /// </summary>
        /// <returns></returns>
        public DataTable GetUserLoginList(string sqlWhere, string sortField, string sortOrder, int page, int pageSize, ref int total)
        {
            var sqlHelper = new SQLHelper() { IsAutoClose = false };
            try
            {
                DataTable dt = null;

                string TbUser = sqlHelper.GetTempTable();
                sb.Clear();
                sb.AppendLine("SELECT A.*,B.UserNo,B.UserName");
                sb.AppendLine("INTO " + TbUser);
                sb.AppendLine("FROM EntUserLoginLog A WITH(NOLOCK)");
                sb.AppendLine("LEFT JOIN EntUser B WITH(NOLOCK) ON A.UserId=B.UserId");
                sb.AppendLine("WHERE 1=1 ");
                if (!CurrentUser.IsAdministrator)
                    sb.AppendLine("AND A.UserId=" + CurrentUser.UserId);
                sb.AppendLine(sqlWhere);
                sqlHelper.ExecuteNonQuery(sb.ToString());

                dt = sqlHelper.GetPageTableList(TbUser, sortField, sortOrder, pageSize, page, ref total);

                return dt;
            }
            finally
            {
                sqlHelper.Close();
            }
        }

        /// <summary>
        /// 获取用户操作日志
        /// </summary>
        /// <returns></returns>
        public DataTable GetUserLogList(string sqlWhere, string sortField, string sortOrder, int page, int pageSize, ref int total)
        {
            var sqlHelper = new SQLHelper() { IsAutoClose = false };
            try
            {
                DataTable dt = null;

                string TbUser = sqlHelper.GetTempTable();
                sb.Clear();
                sb.AppendLine("SELECT A.*,B.UserNo,B.UserName,DATEDIFF(MILLISECOND,A.RequestBeginTime,A.RequestEndTime) AS DiffTime");
                sb.AppendLine("INTO " + TbUser);
                sb.AppendLine("FROM EntLog A WITH(NOLOCK)");
                sb.AppendLine("LEFT JOIN EntUser B WITH(NOLOCK) ON A.UserId=B.UserId");
                sb.AppendLine("WHERE 1=1 ");
                if (!CurrentUser.IsAdministrator)
                    sb.AppendLine("AND A.UserId=" + CurrentUser.UserId);
                sb.AppendLine(sqlWhere);
                sqlHelper.ExecuteNonQuery(sb.ToString());

                dt = sqlHelper.GetPageTableList(TbUser, sortField, sortOrder, pageSize, page, ref total);

                return dt;
            }
            finally
            {
                sqlHelper.Close();
            }
        }

    }
}
